#-*- encoding: utf-8 -*-

from Import.TradeInfoForNB import *
import os
import shutil 
import win32com.client

from DataAccess.QueryBlp import *

from Config.ImportConfig import *
from Config.ReportConfig import *

def extract_trade_for_NB(dir_path = NB_CONF_READ_DIR_PATH):
	
	if not os.path.isdir(dir_path):
		raise RuntimeError(), "Failed to open the dir " + dir_path
	
	#Output to excel
	xApp = win32com.client.Dispatch("Excel.Application")
	xBook = xApp.Workbooks.Open(NB_XLS_TRADE_RECORD_PATH)
	xSht = xBook.Worksheets('Trade Check')
	row_front = xSht.Range("A65536").End(win32com.client.constants.xlUp).Row + 1
	row_back = row_front
	
	#trade file for JP Morgan
	tplt_file = TPLT_PATH + 'Norges Trade Import.xls'
	xBook_jpm = xApp.Workbooks.Open(tplt_file)
	xSht_jpm = xBook_jpm.Worksheets('Report')
	row_jpm = 1
	
	#
	trd_front_NB = None
	trd_back_NB = None
	
	for elem in os.listdir(dir_path):
		file_prefix = os.path.splitext(elem)[0]
		file_suffix = os.path.splitext(elem)[1]
		abs_path = os.path.join(dir_path, elem)
		
		if file_prefix[:28] == 'Exec_NBIM PF EX EQ SP Finals' and  file_suffix[1:].lower() == 'xls':
		
			trd_front_NB = extract_trade_from_UBS_front_office(abs_path)
			if trd_front_NB:
				for elem in trd_front_NB:
					xSht.Range(xSht.Cells(row_front, 1), xSht.Cells(row_front, 6)).Value = elem
					row_front += 1	
				print 'info, read ubs front office trade file completed'			
			
		elif file_prefix[:37] == '000000007305_QFII_execution_inx level' and  file_suffix[1:].lower() == 'xls':
		
			trd_back_NB = extract_trade_from_UBS_back_office(abs_path)
			if trd_back_NB:
				for elem in trd_back_NB:
					xSht.Range(xSht.Cells(row_back, 9), xSht.Cells(row_back, 25)).Value = elem
					row_back += 1
				print 'info, read ubs back office trade file completed'	
				
				#create trade file for JP Morgan	
				for elem in trd_back_NB:
					blp_tick = elem[1] + ' CH Equity'
					r = query_blp_static([blp_tick], ['NAME','ID_ISIN'])
					xSht_jpm.Cells(row_jpm, 1).Value = 'ADM68'
					xSht_jpm.Cells(row_jpm, 2).Value = 'RVP' if elem[2] == 'BUY' else 'DVP'
					xSht_jpm.Cells(row_jpm, 3).Value = elem[3]*elem[4]
					xSht_jpm.Cells(row_jpm, 4).Value = 'EQU'
					xSht_jpm.Cells(row_jpm, 5).Value = elem[0].strftime('%m/%d/%Y')
					xSht_jpm.Cells(row_jpm, 6).Value = elem[7].strftime('%m/%d/%Y')
					xSht_jpm.Cells(row_jpm, 7).Value = 'ISIN'
					xSht_jpm.Cells(row_jpm, 8).Value = r[blp_tick]['ID_ISIN']
					xSht_jpm.Cells(row_jpm, 9).Value = r[blp_tick]['NAME']
					xSht_jpm.Cells(row_jpm, 10).Value = 'UBSUCNB1'
					xSht_jpm.Cells(row_jpm, 12).Value = 'UBSUCNB1'
					xSht_jpm.Cells(row_jpm, 14).Value = elem[3]
					xSht_jpm.Cells(row_jpm, 15).Value = elem[8]
					xSht_jpm.Cells(row_jpm, 16).Value = elem[11]
					xSht_jpm.Cells(row_jpm, 51).Value = elem[5]
					xSht_jpm.Cells(row_jpm, 53).Value = elem[3]*elem[8]
					xSht_jpm.Cells(row_jpm, 59).Value = elem[12] + elem[13] + elem[14] + elem[15]
					xSht_jpm.Cells(row_jpm, 108).Value = 'I'
					xSht_jpm.Cells(row_jpm, 109).Value = 'C'
					xSht_jpm.Cells(row_jpm, 159).Value = 'SSCCCNS1' if (blp_tick[0] == '0' or blp_tick[0] == '3') else 'SSCECNS1'
					row_jpm += 1
		else:
			print 'Warning: File is not CPPIB Trading Files: ' + elem
			continue
			
		shutil.copy2(abs_path, NB_CONF_BACKUP_DIR_PATH)
		os.remove(abs_path)
	#create trade file for JP Morgan
	trade_date = trd_back_NB[0][0]
	rept_file = REPT_PATH + 'TI file import_' + trade_date.strftime("%Y%m%d") + '.csv'
	xBook_jpm.SaveAs(rept_file, win32com.client.constants.xlCSV)
	xBook_jpm.Close()
	print 'info, norges daily trade report completed'
	
	xBook.Save()
	xBook.Close()
	
	del xApp

if __name__ == '__main__':

	extract_trade_for_NB()